# Copyright (C) 2026. Logiciel élaboré par l'État, via le DEPS.
# Nom des auteures : Flore Cornuet et Camille Parent, DEPS.
# Ce programme informatique a été développé par le DEPS.
# Il permet de produire les résultats du Culture Études 1-2026.

# Ce programme a été exécuté en septembre 2026 avec la version 4.5.3 de R.
# La publication peut être consultée sur le site du Deps. 
# Ce programme utilise les données du pass Culture et du recensement de la population 2022.

# Bien qu'il n'existe aucune obligation légale à ce sujet, les utilisateurs de ce programme sont
# invités à signaler au DEPS leurs travaux issus de la réutilisation de ce code, ainsi que les
# éventuels problèmes ou anomalies qu'ils y rencontreraient, en écrivant à contact.deps@culture.gouv.fr .
# Ce logiciel est régi par la licence EUPL V1.2 soumise au droit français et européen, respectant
# les principes de diffusion des logiciels libres. Vous pouvez utiliser, modifier et/ou redistribuer
# ce programme sous les conditions de la licence EUPL V1.2, telle que diffusée sur
# https://eupl.eu/1.2/fr/ .
# En contrepartie de l'accessibilité au code source et des droits de copie, de modification et de
# redistribution accordés par cette licence, il n'est offert aux utilisateurs qu'une garantie
# limitée. Pour les mêmes raisons, seule une responsabilité restreinte pèse sur les auteures du
# programme, le titulaire des droits patrimoniaux et les concédants successifs.
# À cet égard l'attention de l'utilisateur est attirée sur les risques associés au chargement, à
# l'utilisation, à la modification et/ou au développement et à la reproduction du logiciel par
# l'utilisateur étant donné sa spécificité de logiciel libre, qui peut le rendre complexe à
# manipuler et qui le réserve donc à des développeurs et des professionnels avertis possédant des
# connaissances informatiques approfondies. Les utilisateurs sont donc invités à charger et tester
# l'adéquation du logiciel à leurs besoins dans des conditions permettant d'assurer la sécurité de
# leurs systèmes et ou de leurs données et, plus généralement, à l'utiliser et l'exploiter dans les
# mêmes conditions de sécurité.


# création d'une table réduite où on a le nombre d'user_id différent selon
# deposit_amount : 300 ou autre
# deposit_creation_date : entre le 21 mai 2021 et 28 février 2025
# deposit_expiration_date : avant ou après le 28 février 2025
# reservation : a fait au moins une dépense ou non (présent dans la table booking + booking_status %in% c('USED'|'REIMBURSED')
# +booking_creation_date < 28 février 2025)


# chargement des packages ####

library(arrow)
library(duckdb)
library(dplyr)
library(data.table)
library(readxl)
library(tidyverse)


# connexion DuckDB ####
con <- dbConnect(duckdb())
dbExecute(con, "SET threads = 4")
dbExecute(con, "SET preserve_insertion_order = false")
dbExecute(con, "SET memory_limit = '16GB'")
dbExecute(con, "PRAGMA enable_progress_bar")

# enregistrement arrow ####
duckdb_register_arrow(con, "user", open_dataset(paste0(chemin_donnees, "exp_ministry__user/")))
duckdb_register_arrow(con, "deposit", open_dataset(paste0(
  chemin_donnees, "exp_ministry__deposit/"
)))
duckdb_register_arrow(con, "user_addr", open_dataset(paste0(
  chemin_donnees, "exp_ministry__user_address/"
)))
duckdb_register_arrow(con, "offer_dict", open_dataset(paste0(chemin_donnees, "offer_dict.parquet")))

chemin_booking <- paste0(chemin_donnees, "exp_ministry__booking/*.parquet")

# tab_finale ####

# === Etape 1 : user_clean ===
dbExecute(
  con,
  "
  CREATE OR REPLACE TABLE user_clean AS
  SELECT
    user_id,
    YEAR(user_birth_date) AS annee_naissance,
    CASE
      WHEN user_civility = 'M.'   THEN 'H'
      WHEN user_civility = 'Mme.' THEN 'F'
      ELSE 'Autre'
    END AS sexe
  FROM user
  WHERE YEAR(user_birth_date) BETWEEN 2000 AND 2015
"
)

# === Etape 2 : création de deposit_creation et deposit_expiration
dbExecute(
  con,
  "
  CREATE OR REPLACE TABLE deposit_flag AS
  SELECT
    user_id,
    deposit_id,
    CASE WHEN deposit_amount = 300 THEN 1 ELSE 0 END AS a_depot_300,
    CASE WHEN deposit_creation_date >= '2021-05-21'
          AND deposit_creation_date <  '2025-02-28' THEN 1 ELSE 0 END AS deposit_creation,
    CASE WHEN deposit_expiration_date < '2025-02-28'  THEN 1 ELSE 0 END AS deposit_expiration
  FROM deposit
"
)

# === Etape 3 : somme des depsnes par user_id validées avant le 28 fev 2025
dbExecute(
  con,
  paste0(
    "
  CREATE OR REPLACE TABLE booking_agg AS
  SELECT
    b.user_id,
    b.deposit_id,
    COALESCE(od.offer_category, 'sans catégorie') AS offer_category,
    SUM(b.booking_intermediary_amount) AS sum_intermediary_amount,
    1 AS a_depense
  FROM read_parquet('",
    chemin_booking,
    "') b
  LEFT JOIN offer_dict od ON b.offer_id = od.offer_id
  WHERE b.booking_creation_date < '2025-02-28'
    AND b.booking_status IN ('used', 'reimbursed')
    AND b.booking_intermediary_amount > 0
  GROUP BY b.user_id, b.deposit_id, COALESCE(od.offer_category, 'sans catégorie')
"
  )
)

# === Etape 4a : on attribue les depenses validées aux deposit_id
tab_finale <- dbGetQuery(
  con,
  "
  SELECT
    ua.user_department_code,
    ua.user_region_name,
    uc.annee_naissance,
    uc.sexe,
    df.a_depot_300,
    df.deposit_creation,
    df.deposit_expiration,
    COALESCE(ba.a_depense, 0) AS a_depense,
    COUNT(DISTINCT uc.user_id)                       AS nb_users,
    SUM(COALESCE(ba.sum_intermediary_amount, 0))     AS sum_intermediary_amount
  FROM user_clean uc
  LEFT JOIN user_addr    ua ON uc.user_id = ua.user_id
  LEFT JOIN deposit_flag df ON uc.user_id = df.user_id
  LEFT JOIN (
    SELECT user_id, deposit_id,
           MAX(a_depense)              AS a_depense,
           SUM(sum_intermediary_amount) AS sum_intermediary_amount
    FROM booking_agg
    GROUP BY user_id, deposit_id
  ) ba ON uc.user_id = ba.user_id AND df.deposit_id = ba.deposit_id
  GROUP BY
    ua.user_department_code,
    ua.user_region_name,
    uc.annee_naissance,
    uc.sexe,
    df.a_depot_300,
    df.deposit_creation,
    df.deposit_expiration,
    a_depense
  ORDER BY
    ua.user_department_code,
    uc.annee_naissance
"
)

# tab_cat ####

# === Etape 4b : agregation avec catégorie ===
tab_cat <- dbGetQuery(
  con,
  "
  SELECT
    ua.user_department_code,
    ua.user_region_name,
    uc.annee_naissance,
    uc.sexe,
    df.a_depot_300,
    df.deposit_creation,
    df.deposit_expiration,
    COALESCE(ba.a_depense, 0) AS a_depense,
    ba.offer_category,
    COUNT(DISTINCT uc.user_id)                       AS nb_users,
    SUM(COALESCE(ba.sum_intermediary_amount, 0))     AS sum_intermediary_amount
  FROM user_clean uc
  LEFT JOIN user_addr    ua ON uc.user_id = ua.user_id
  LEFT JOIN deposit_flag df ON uc.user_id = df.user_id
  LEFT JOIN booking_agg  ba ON uc.user_id = ba.user_id AND df.deposit_id = ba.deposit_id
  GROUP BY
    ua.user_department_code,
    ua.user_region_name,
    uc.annee_naissance,
    uc.sexe,
    df.a_depot_300,
    df.deposit_creation,
    df.deposit_expiration,
    a_depense,
    ba.offer_category
  ORDER BY
    ua.user_department_code,
    uc.annee_naissance,
    ba.offer_category
"
)



# on change le nom des variables ####

colnames(tab_finale)

class(tab_finale)
setnames(
  tab_finale,
  c(
    'a_depot_300',
    'a_depense',
    'deposit_creation',
    'deposit_expiration',
    'nb_users',
    'sum_intermediary_amount'
  ),
  c(
    'chq_300',
    'utilise',
    'champ_creation',
    'champ_expiration',
    'nb_user',
    'total_depense'
  )
)

colnames(tab_cat)

setnames(
  tab_cat,
  c(
    'a_depot_300',
    'a_depense',
    'deposit_creation',
    'deposit_expiration',
    'offer_category',
    'nb_users',
    'sum_intermediary_amount'
  ),
  c(
    'chq_300',
    'utilise',
    'champ_creation',
    'champ_expiration',
    'cat_depense',
    'nb_user',
    'total_depense'
  )
)



# === Sauvegarde ===
write_parquet(tab_finale,
              paste0(chemin_intermediaires, "tab_finale.parquet"))
write_parquet(tab_cat, paste0(chemin_intermediaires, "tab_cat.parquet"))

setDT(tab_finale)


# tab_categories ####

# === Enregistrement Arrow ===
duckdb_register_arrow(con, "user", open_dataset(paste0(chemin_donnees, "exp_ministry__user/")))
duckdb_register_arrow(con, "deposit", open_dataset(paste0(
  chemin_donnees, "exp_ministry__deposit/"
)))
duckdb_register_arrow(con, "offer_dict", open_dataset(paste0(chemin_donnees, "offer_dict.parquet")))

chemin_booking <- paste0(chemin_donnees, "exp_ministry__booking/*.parquet")

# === Etape 1 : user_clean ===
message("Etape 1/4 : user_clean")
dbExecute(
  con,
  "
  CREATE OR REPLACE TABLE user_clean AS
  SELECT
    user_id,
    YEAR(user_birth_date) AS annee_naissance,
    CASE
      WHEN user_civility = 'M.'   THEN 'H'
      WHEN user_civility = 'Mme.' THEN 'F'
      ELSE 'Autre'
    END AS sexe
  FROM user
  WHERE YEAR(user_birth_date) BETWEEN 2002 AND 2007
"
)

# === Etape 2 : deposit_flag (une ligne par deposit) ===
message("Etape 2/4 : deposit_flag")
dbExecute(
  con,
  "
  CREATE OR REPLACE TABLE deposit_flag AS
  SELECT
    user_id,
    deposit_id,
    CASE WHEN deposit_amount = 300 THEN 1 ELSE 0 END AS chq_300,
    CASE WHEN deposit_creation_date >= '2021-05-21'
          AND deposit_creation_date <  '2025-02-28' THEN 1 ELSE 0 END AS champ_creation,
    CASE WHEN deposit_expiration_date < '2025-02-28' THEN 1 ELSE 0 END AS champ_expiration
  FROM deposit
"
)

# === Etape 3 : booking_agg par user x deposit x categorie ===
message("Etape 3/4 : booking_cat")
dbExecute(
  con,
  paste0(
    "
  CREATE OR REPLACE TABLE booking_cat AS
  SELECT
    b.user_id,
    b.deposit_id,
    MAX(CASE WHEN od.offer_category = 'LIVRE'               THEN 1 ELSE 0 END) AS LIVRE,
    MAX(CASE WHEN od.offer_category = 'CINEMA'              THEN 1 ELSE 0 END) AS CINEMA,
    MAX(CASE WHEN od.offer_category = 'MUSIQUE_LIVE'        THEN 1 ELSE 0 END) AS MUSIQUE_LIVE,
    MAX(CASE WHEN od.offer_category = 'MUSIQUE_ENREGISTREE' THEN 1 ELSE 0 END) AS MUSIQUE_ENREGISTREE,
    MAX(CASE WHEN od.offer_category = 'INSTRUMENT'          THEN 1 ELSE 0 END) AS INSTRUMENT,
    MAX(CASE WHEN od.offer_category = 'FILM'                THEN 1 ELSE 0 END) AS FILM,
    MAX(CASE WHEN od.offer_category = 'SPECTACLE'           THEN 1 ELSE 0 END) AS SPECTACLE,
    MAX(CASE WHEN od.offer_category = 'JEU'                 THEN 1 ELSE 0 END) AS JEU,
    MAX(CASE WHEN od.offer_category = 'BEAUX_ARTS'          THEN 1 ELSE 0 END) AS BEAUX_ARTS,
    MAX(CASE WHEN od.offer_category = 'MUSEE'               THEN 1 ELSE 0 END) AS MUSEE,
    MAX(CASE WHEN od.offer_category = 'PRATIQUE_ART'        THEN 1 ELSE 0 END) AS PRATIQUE_ART,
    MAX(CASE WHEN od.offer_category = 'CONFERENCE'          THEN 1 ELSE 0 END) AS CONFERENCE,
    MAX(CASE WHEN od.offer_category = 'MEDIA'               THEN 1 ELSE 0 END) AS MEDIA,
    MAX(CASE WHEN od.offer_category = 'CARTE_JEUNES'        THEN 1 ELSE 0 END) AS CARTE_JEUNES,
    MAX(CASE WHEN od.offer_category = 'TECHNIQUE'           THEN 1 ELSE 0 END) AS TECHNIQUE,
    MAX(CASE WHEN od.offer_category IS NULL THEN 1 ELSE 0 END) AS SANS_CATEGORIE
  FROM read_parquet('",
    chemin_booking,
    "') b
  LEFT JOIN offer_dict od ON b.offer_id = od.offer_id
  WHERE b.booking_creation_date < '2025-02-28'
    AND b.booking_status IN ('used', 'reimbursed')
    AND b.booking_intermediary_amount > 0
  GROUP BY b.user_id, b.deposit_id
"
  )
)

# === Etape 4 : agregation finale ===
message("Etape 4/4 : agregation finale")
tab_categories <- dbGetQuery(
  con,
  "
  SELECT
    uc.annee_naissance,
    uc.sexe,
    df.chq_300,
    df.champ_creation,
    df.champ_expiration,
    COALESCE(bc.LIVRE,               0) AS LIVRE,
    COALESCE(bc.CINEMA,              0) AS CINEMA,
    COALESCE(bc.MUSIQUE_LIVE,        0) AS MUSIQUE_LIVE,
    COALESCE(bc.MUSIQUE_ENREGISTREE, 0) AS MUSIQUE_ENREGISTREE,
    COALESCE(bc.INSTRUMENT,          0) AS INSTRUMENT,
    COALESCE(bc.FILM,                0) AS FILM,
    COALESCE(bc.SPECTACLE,           0) AS SPECTACLE,
    COALESCE(bc.JEU,                 0) AS JEU,
    COALESCE(bc.BEAUX_ARTS,          0) AS BEAUX_ARTS,
    COALESCE(bc.MUSEE,               0) AS MUSEE,
    COALESCE(bc.PRATIQUE_ART,        0) AS PRATIQUE_ART,
    COALESCE(bc.CONFERENCE,          0) AS CONFERENCE,
    COALESCE(bc.MEDIA,               0) AS MEDIA,
    COALESCE(bc.CARTE_JEUNES,        0) AS CARTE_JEUNES,
    COALESCE(bc.TECHNIQUE,           0) AS TECHNIQUE,
    COALESCE(bc.SANS_CATEGORIE, 0) AS SANS_CATEGORIE,
    COUNT(DISTINCT uc.user_id)          AS nb_users
  FROM user_clean uc
  LEFT JOIN deposit_flag df ON uc.user_id = df.user_id
  LEFT JOIN booking_cat  bc ON uc.user_id = bc.user_id AND df.deposit_id = bc.deposit_id
  GROUP BY
    uc.annee_naissance,
    uc.sexe,
    df.chq_300,
    df.champ_creation,
    df.champ_expiration,
    bc.LIVRE, bc.CINEMA, bc.MUSIQUE_LIVE, bc.MUSIQUE_ENREGISTREE,
    bc.INSTRUMENT, bc.FILM, bc.SPECTACLE, bc.JEU, bc.BEAUX_ARTS,
    bc.MUSEE, bc.PRATIQUE_ART, bc.CONFERENCE, bc.MEDIA,
    bc.CARTE_JEUNES, bc.TECHNIQUE,
    bc.CARTE_JEUNES, bc.TECHNIQUE, bc.SANS_CATEGORIE
  ORDER BY
    uc.annee_naissance,
    uc.sexe
"
)

setDT(tab_categories)

# === Sauvegarde ===
write_parquet(tab_categories,
              paste0(chemin_intermediaires, "tab_categories.parquet"))
